﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace KaoWuYou.Models
{
    public class TestPaperModel
    {
        public int QuestionID
        { set; get; }
        [Required]
        public List<QuestionModel> Questions
        { set; get; }
        [Required]
        public int SubclassID
        { set; get; }
        [Required]
        public int SubjectID
        { set; get; }
        public int TestPaperID
        { set; get; }
        public string TestPaperName
        { set; get; }

        public bool AddQuestionID()
        {
            try
            {
                SqlConnection conn = new SqlConnection(Systems.ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO Test-Question(TestPaperID, QuestionID)  VALUES (@TestPaperID, @QuestionID)";
                cmd.Parameters.AddRange(new SqlParameter[] 
                { 
                    new SqlParameter("@QuestionID", QuestionID) ,
                    new SqlParameter("@TestPaperID", TestPaperID)
                });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.NextResult())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    this.Questions.Add(tmp);
                }
                sdr.Close();
                conn.Close();
                return true;
            }
            catch 
            {
                return false;
            }
        }

        public bool DeleteQuestionID()
        {
            try
            {
                SqlConnection conn = new SqlConnection(Systems.ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM Test-Question WHERE QuestionID = @QuestionID";
                cmd.Parameters.AddRange(new SqlParameter[] 
                { 
                    new SqlParameter("@QuestionID", QuestionID) ,
                });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.NextResult())
                {
                    QuestionModel tmp = new QuestionModel()
                    {
                        AnswerKey = sdr["AnswerKey"] as string,
                        Genre = sdr["Genre"] as string,
                        QuestionID = (int)sdr["QuestionID"],
                        Stem = sdr["Stem"] as string,
                        SubclassID = (int)sdr["SubclassID"],
                        SubjectID = (int)sdr["SubjectID"]
                    };
                    this.Questions.Add(tmp);
                }
                sdr.Close();
                conn.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        public void FindOwnedQuestions()
        {
            SqlConnection conn = new SqlConnection(Systems.ConnString);
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT * FROM Test-Question WHERE TestPaperID = @TestPaperID";
            cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@TestPaperID", TestPaperID) 
            });
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.NextResult())
            {
                QuestionModel tmp = new QuestionModel()
                {
                    AnswerKey = sdr["AnswerKey"] as string,
                    Genre = sdr["Genre"] as string,
                    QuestionID = (int)sdr["QuestionID"],
                    Stem = sdr["Stem"] as string,
                    SubclassID = (int)sdr["SubclassID"],
                    SubjectID = (int)sdr["SubjectID"]
                };
                this.Questions.Add(tmp);
            }
            sdr.Close();
            conn.Close();
        }

        public void ImportTestPaper(string testPaperFilePath)
        {
            throw new NotImplementedException();
        }
    }
}